package news.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import school.util.DBUtil;
import news.entity.Topic;

public class TopicDao {
	public static final TopicDao DAO = new TopicDao();

	/**
	 * 查询所有主题
	 * 
	 * @return
	 */
	public List<Topic> findAll() {
		List<Topic> li = new ArrayList<Topic>();
		Topic t = new Topic();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet r = null;
		String sql = "select * from topic";
		con = DBUtil.getConnection();
		try {
			ps = con.prepareStatement(sql);
			r = ps.executeQuery();
			while (r.next()) {
				t = new Topic();
				t.setTid(r.getInt("tid"));
				t.setTname(r.getString("tname"));
				li.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(r, ps, con);
		}

		return li;
	}

	/**
	 * 查询指定主题
	 * 
	 * @return
	 */
	public Topic find(int id) {
		Topic t = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet r = null;
		String sql = "select * from topic where tid=?";
		con = DBUtil.getConnection();
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			r = ps.executeQuery();
			while (r.next()) {
				t = new Topic();
				t.setTid(r.getInt("tid"));
				t.setTname(r.getString("tname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(r, ps, con);
		}

		return t;
	}

	/**
	 * 根据序号删除
	 */
	public int del(int num) {
		int index = 1;
		Connection con = null;
		PreparedStatement ps = null;
		int r = 0;
		con = DBUtil.getConnection();
		String sql = "delete from tipic where tid=? ";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(index++, num);
			r = ps.executeUpdate();
			System.out.println("删除了" + r + "条");
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;
	}

	/**
	 * 根据序号修改主题
	 */
	public int update(int num, String content) {
		int index = 1;
		Connection con = null;
		PreparedStatement ps = null;
		int r = 0;
		con = DBUtil.getConnection();
		String sql = "update topic set tname=? where num=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(index++, content);
			ps.setInt(index++, num);
			r = ps.executeUpdate();
			System.out.println("更新了" + r + "条");
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return 0;
	}

	/**
	 * 获得置顶分类
	 * 
	 * @return
	 */
	public Topic find(String tname) {
		Topic t = new Topic();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet r = null;
		String sql = "select * from topic where tname=?";
		con = DBUtil.getConnection();
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, tname);
			r = ps.executeQuery();
			while (r.next()) {
				t = new Topic();
				t.setTid(r.getInt("tid"));
				t.setTname(r.getString("tname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(r, ps, con);
		}

		return t;
	}

}
